package com.dao;

import com.bean.Group;
import com.bean.User;
import com.db.DBUtil;
import com.util.DataUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GroupDao {
    DBUtil dbUtil = new DBUtil();


    /**
     * 保存群组
     * @param group
     */
    public void saveGroup(Group group){
        int primaryId=0;
        String sql="insert into groups(user_id,name,qq) values" +
                "("+group.getUserId()+","
                +"'"+group.getName()+"',"
                +"'"+group.getQq()+"'" +
                ")";
        try {
            primaryId = dbUtil.insert(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbUtil.close();
        }
        if(primaryId>0){
            saveUserToGroup(primaryId,group.getUserId());
        }
    }

    /**
     *添加人员如群
     * @param groupId
     * @param userId
     */
    public void saveUserToGroup(int groupId,int userId){
        String sql0 = "select count(1) as num from group_user where group_id="+groupId+" and user_id="+userId;
        String sql="insert into group_user(group_id,user_id) values" +
                "("+groupId+","+userId+")";
        try {
            ResultSet result = dbUtil.query(sql0);
            while (result.next()){
               int num = result.getInt("num");
                if(num<1){
                    dbUtil.insertOrUpdate(sql);
                }
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbUtil.close();
        }
    }

    /**
     * 获取用户所有的组
     * @return
     */
    public List<Group> getGroupByUser(int userId){
        String sql="select t1.* from groups t1 inner join group_user t2 on t1.id = t2.group_id where t2.user_id="+userId;
       List<Group> list= new ArrayList<>();
        try {
            ResultSet result=dbUtil.query(sql);
            while (result.next()){
                Group c = new Group();
                c.setId(result.getInt("id"));
                c.setUserId(result.getInt("user_id"));
                c.setName(result.getString("name"));
                c.setQq(result.getString("qq"));
                c.setCreateTiem(DataUtils.dateToString(result.getDate("createtime")));
                c.setUserList(findUser(result.getInt("id")));
                list.add(c);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbUtil.close();
        }
        return list;


    }

    /**
     * 查看群组是否已经存在
     * @param groupQQ
     * @return
     */
    public int isEXistGroup(String groupQQ){
        String sql="select count(1) as num from  groups where qq='"+groupQQ+"'";
        int num=0;
        try {
            ResultSet result=dbUtil.query(sql);
            while (result.next()){
                num =result.getInt("num");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbUtil.close();
        }
        return num;


    }

    /**
     * 获取一个群组通过id
     * @param groupId
     */
    public Group getGroupOne(int groupId){

            String sql="select * from  groups where id="+groupId;
            Group c = new Group();
            try {
                ResultSet result=dbUtil.query(sql);
                while (result.next()){
                    c.setId(result.getInt("id"));
                    c.setUserId(result.getInt("user_id"));
                    c.setName(result.getString("name"));
                    c.setQq(result.getString("qq"));
                    c.setCreateTiem(DataUtils.dateToString(result.getDate("createtime")));
                    c.setUserList(findUser(groupId));
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                dbUtil.close();
            }
            return c;
    }
    /**
     * 获取一个群组通过群号
     * @param groupQQ
     */
    public Group getGroupOneByQQ(String groupQQ){

        String sql="select * from  groups where qq='"+groupQQ+"'";
        Group c = new Group();
        try {
            ResultSet result=dbUtil.query(sql);
            while (result.next()){
                c.setId(result.getInt("id"));
                c.setUserId(result.getInt("user_id"));
                c.setName(result.getString("name"));
                c.setQq(result.getString("qq"));
                c.setCreateTiem(DataUtils.dateToString(result.getDate("createtime")));
                c.setUserList(findUser(result.getInt("id")));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbUtil.close();
        }
        return c;
    }

    /**
     * 查找群组内的所以成员
     * @param groupId
     * @return
     */
    public List<User> findUser(int groupId) {
        String sql="select t1.* from users t1 inner join group_user t2 on t1.id = t2.user_id where t2.group_id="+groupId;
       List<User> list = new ArrayList<>();
        try {
            ResultSet result=dbUtil.query(sql);
            while (result.next()){
                User c = new User();
                c.setId(result.getInt("id"));
                c.setUsername(result.getString("username"));
                c.setPassword(result.getString("password"));
                c.setAge(result.getString("age"));
                c.setHeadPic(result.getString("headpic"));
                c.setSex(result.getString("sex"));
                c.setQq(result.getString("qq"));
                list.add(c);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 退出群组
     * @param groupId
     * @param userId
     */
    public void deleteUser(int groupId, int userId) {
        String sql="delete from  group_user where group_id=" +groupId+
                " and user_id="+userId;
        try {
            dbUtil.insertOrUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbUtil.close();
        }
    }
}
